package com.bct.jxc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bct.jxc.common.Constants;
import com.bct.jxc.common.ErrorManager;
import com.bct.jxc.common.StringUtil;
import com.bct.jxc.model.Customer;
import com.bct.jxc.model.Product;
import com.bct.jxc.model.ProductInfo;
import com.bct.jxc.model.Provider;

/**
 * 商品类类Dao
 * 针对(t_product表进行CRUD)
 * @author ASUS
 */
public class ProductDao implements BaseDao<Product>{
SqlManager manager = null;
	
	public ProductDao() {
		super();
		manager = SqlManager.createInstance();
		manager.connectDB();//获取数据库连接
	}
	/**
	 * 添加商品信息
	 * @param product
	 * @return
	 */
	@Override
	public boolean add(Product product) {
		//编写sql语句
		String sql = "insert into t_product values(?,?,?,?,?,?,?,?,?,?,?,1)";
		//封装参数
		Object[] params = {product.getId(),product.getProductName(),product.getShorts(),product.getPlace(),
				product.getSpecification(),product.getPacking(),product.getBatch(),product.getApprovalNumber(),
				product.getPrice(),product.getRemark(),product.getProviderId()};
		return manager.executeUpdate(sql, params, Constants.PSTM_TYPE);
	}
	/**
	 * 生成商品编号
	 * @return
	 */
	@Override
	public String getId() {
		//sql语句，获得最新的商品编号
		String sql = "select MAX(id) as id from t_product";
		ResultSet rs = manager.executeQuery(sql, null, Constants.PSTM_TYPE);
		String id = "PD1001";
		try {
			if(rs != null && rs.next()) {
				String sid = rs.getString("id");// 获取前一次插入的id
				if(sid != null) {
					String s = sid.substring(2);
					id = "PD" + (Integer.parseInt(s) + 1);
				}
			}
		} catch (SQLException e) {
			ErrorManager.printError("ProductDao getProductId()", e);
		}
		return id;
	}
	/**
	 * 根据商品编号修改供应商信息
	 * @param provider
	 * @return
	 */
	@Override
	public boolean update(Product product) {
		//编写sql语句
		String sql = "update t_product set product_name=?,shorts=?,place=?,specification=?,"
				+ "packing=?,batch=?,approval_number=?,price=?,remark=?,provider_id=? where id=?";
		String id = product.getId();
		String productName = product.getProductName();
		String shorts = product.getShorts();
		String place = product.getPlace();
		String specification = product.getSpecification();
		String packing = product.getPacking();
		String batch = product.getBatch();
		String approvalNumber = product.getApprovalNumber();
		float price = product.getPrice();
		String remark = product.getRemark();
		String providerId = product.getProviderId();
		//判断用户修改的供应商编号是否存在,随后跟界面合并时，再修改
		
		//封装参数
		Object params[] = {productName,shorts,place,specification,packing,batch,approvalNumber,price,remark,providerId,id};
		return manager.executeUpdate(sql, params, Constants.PSTM_TYPE);
	}
	/**
	 * 根据商品编号修改商品spare
	 * @param id
	 * @param spare 0表示删除，1表示找回
	 * @return
	 */
	@Override
	public boolean update(String id,Integer spare) {
		//编写sql语句
		String sql = "update t_product set spare=? where id=?";
		//封装参数
		Object params[] = {spare!=null?spare:1,id};
		return manager.executeUpdate(sql, params, Constants.PSTM_TYPE);
	}
	/**
	 * 查询商品信息
	 * @param product
	 * @return
	 */
	@Override
	public List<Product> finds(Product product){
		List<Product> list = null;
		String sql = null;
		Object[] params = null;
		if(product == null) {
			sql = "select * from t_product where spare=1";
		}else {
			//根据ID查询
			if(!StringUtil.isEmpty(product.getId())) {
				sql = "select * from t_product where id=? and spare = 1";
				params = new Object[] {product.getId()};
			}else if(!StringUtil.isEmpty(product.getProductName())) {//根据商品全称查询
				sql = "select * from t_product where product_name like ? and spare = 1";
				params = new Object[] {"%"+product.getProductName()+"%"};
			}else if(!StringUtil.isEmpty(product.getShorts())) {//根据商品简称查询
				sql = "select * from t_product where shorts like ? and spare = 1";
				params = new Object[] {"%"+product.getShorts()+"%"};
			}
		}
		ResultSet rs = manager.executeQuery(sql, params, Constants.PSTM_TYPE);
		if(rs != null) {
			list = new ArrayList<Product>();
			try {
				while(rs.next()) {
					String id = rs.getString("id");
					String productName = rs.getString("product_name");
					String shorts = rs.getString("shorts");
					String place = rs.getString("place");
					String specification = rs.getString("specification");
					String packing = rs.getString("packing");
					String batch = rs.getString("batch");
					String approvalNumber = rs.getString("approval_number");
					float price = Float.parseFloat(rs.getString("price"));
					String remark = rs.getString("remark");
					String providerId = rs.getString("provider_id");
					int spare = Integer.parseInt(rs.getString("spare"));
					Product p = new Product(id, productName, shorts, place, specification, packing, batch, approvalNumber, price, remark, providerId, spare);
					list.add(p);
				}
			} catch (SQLException e) {
				ErrorManager.printError("ProductDao findProducts()", e);
			}
		}
		return list;
	}
	/**
	 * 查询商品
	 */
	public List<ProductInfo> finds(ProductInfo p) {
		List<ProductInfo> list = null;
		String sql = null;
		Object[] params = null;
		if (p == null) {// 没有参数
			// 编写sql
			sql = "select g.id,g.product_name,g.shorts,g.place,g.specification,g.packing,g.batch,g.approval_number," + 
					"g.price,g.remark,g.provider_id,p.provider_name from t_product g,t_provider p " + 
					" where g.spare=1 and g.provider_id=p.id";
		} else {// 有参数，等待以后扩展
			// 根据商品ID查询
			if (!StringUtil.isEmpty(p.getId())) {
				sql = "select g.id,g.product_name,g.shorts,g.place,g.specification,g.packing,g.batch,g.approval_number,"+
						"g.price,g.remark,g.provider_id,p.provider_name from t_product g,t_provider p " +
						" where g.spare=1 and g.provider_id=p.id and g.id =?";
				params = new Object[1];
				params[0] =p.getId();
			} else if (!StringUtil.isEmpty(p.getProductName())) {// 根据商品全称查询
				sql = "select g.id,g.product_name,g.shorts,g.place,g.specification,g.packing,g.batch,g.approval_number," + 
						"g.price,g.remark,g.provider_id,p.provider_name from t_product g,t_provider p"+
						" where g.spare=1 and g.provider_id=p.id and g.product_name like ?";
				params = new Object[1];
				params[0] = "%" + p.getProductName() + "%";
			} else if (!StringUtil.isEmpty(p.getShorts())) {// 根据客户简称查询
				sql = "select g.id,g.product_name,g.shorts,g.place,g.specification,g.packing,g.batch,g.approval_number," + 
						" g.price,g.remark,g.provider_id,p.provider_name from t_product g,t_provider p " + 
						" where g.spare=1 and g.provider_id=p.id and g.shorts like ?";
				params = new Object[1];
				params[0] = "%" + p.getShorts() + "%";
			} else if(!StringUtil.isEmpty(p.getProviderName())) {//根据供应商全称查询
				sql = "select g.id,g.product_name,g.shorts,g.place,g.specification,g.packing,g.batch,g.approval_number," + 
						" g.price,g.remark,g.provider_id,p.provider_name from t_product g,t_provider p " + 
						" where g.spare=1 and g.provider_id=p.id and p.provider_name like ?";
				params = new Object[1];
				params[0] = "%" + p.getProviderName() + "%";
			}
		}
		// 执行sql
		ResultSet rs = manager.executeQuery(sql, params, Constants.PSTM_TYPE);
		// 解析结果集
		if (rs != null) {
			list = new ArrayList<ProductInfo>();
			try {
				while (rs.next()) {
					ProductInfo productInfo = new ProductInfo();
					productInfo.setId(rs.getString("id"));
					productInfo.setProductName(rs.getString("product_name"));
					productInfo.setShorts(rs.getString("shorts"));
					productInfo.setPlace(rs.getString("place"));
					productInfo.setSpecification(rs.getString("specification"));
					productInfo.setPacking(rs.getString("packing"));
					productInfo.setBatch(rs.getString("batch"));
					productInfo.setApprovalNumber(rs.getString("approval_number"));
					productInfo.setPrice(rs.getFloat("price"));
					productInfo.setRemark(rs.getString("remark"));
					productInfo.setProviderId(rs.getString("provider_id"));
					productInfo.setProviderName(rs.getString("provider_name"));
					list.add(productInfo);
				}
			} catch (SQLException e) {
				ErrorManager.printError("GoodsDao query()", e);
			}
		}
		return list;
	}
}
